-- This is a demo for UTF-8 collations.
-- © Erland Sommarskog 2024.

-- First create a surrogate-aware collation without UTF-8 support for German. 
CREATE DATABASE German_PhoneBook_100_CS_AS_SC 
       COLLATE German_PhoneBook_100_CS_AS_SC
go
USE German_PhoneBook_100_CS_AS_SC
go
-- Compare nvarchar and varchar. Only the first word looks the same. Characters
-- not in Latin-1 are replaced by fallbacks.
SELECT N'Übergröße' AS XL, N'ćevapčići' AS food, N'中目黒' AS "Naka-Meguro", 
       N'Ｐａｒｉｓ' AS Paris, N'𐌰𐌱𐌲𐌳𐌴😕' AS Gothic
SELECT 'Übergröße' AS XL, 'ćevapčići' AS food, '中目黒' AS "Naka-Meguro", 
       'Ｐａｒｉｓ' AS Paris, '𐌰𐌱𐌲𐌳𐌴😕' AS Gothic
go
-- Now create a UTF8 datbase for German.
CREATE DATABASE German_PhoneBook_100_CS_AS_SC_UTF8 
       COLLATE German_PhoneBook_100_CS_AS_SC_UTF8 
go
USE German_PhoneBook_100_CS_AS_SC_UTF8 
go
-- Now all words come through correctly, also in varchar!
SELECT 'Übergröße' AS XL, 'ćevapčići' AS food, '中目黒' AS "Naka-Meguro", 
       'Ｐａｒｉｓ' AS Paris, '𐌰𐌱𐌲𐌳𐌴😕' AS Gothic
go
-- Add some words to a table, and look at lengths in characters and bytes.
CREATE TABLE words (no int NOT NULL, word varchar(50) NOT NULL)
INSERT words (no, word)
   VALUES(1, 'Deutsch'), (2, 'Übergröße'), (3, 'ćevapčići'), 
         (4, '中目黒'), (5, 'Ｐａｒｉｓ'), (6, '𐌰𐌱𐌲𐌳𐌴😕')
go
SELECT no, word, len(word) as charlen, datalength(word) AS bytelen,
       substring(word, 1, 3) AS FirstThree
FROM   words
ORDER BY no
go
-- "Übergröße" is nine characters. Does that fit in varchar(9)? By now, 
-- you should know what to expect.
CREATE TABLE varchar9(extralarge varchar(9) NOT NULL)
INSERT varchar9(extralarge) VALUES ('Übergröße')

  
